﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using SD.LLBLGen.Pro.ORMSupportClasses;
using SD.LLBLGen.Pro.QuerySpec;
using SD.LLBLGen.Pro.QuerySpec.Adapter;

using VNet.VisiSuite.BusinessLogic.BusinessException;
using VNet.VisiSuite.Common.Logger;

using VNet.VisiSuite.Dal.DatabaseSpecific;
using VNet.VisiSuite.Dal.EntityClasses;
using VNet.VisiSuite.Dal.FactoryClasses;
using VNet.VisiSuite.Dal.HelperClasses;

using VNet.VisiSuite.Service.Contact.Message;
using VNet.VisiSuite.Service.Contact.Message.Base;
using VNet.VisiSuite.Service.Contact.Message.Item;
using VNet.VisiSuite.Service.Contact.Message.ProductionData;
using System.Collections.ObjectModel;

namespace VNet.VisiSuite.BusinessLogic
{
    public class ProductionDataUtil
    {
        private static readonly string SCHEDULE_DOWN_GRAY = "#FF4D4D4D";
        private static readonly int DEFAULT_LOADUNLOAD_TIME = 30;
        private static readonly int DEFAULT_CYCLE_TIME = 60;

        private static string stripWorkOrder(string work_order)
        {
            string val = work_order;
            try
            {
                int idx = work_order.IndexOf('-');
                if (idx >= 0)
                {
                    idx = work_order.IndexOf('-', idx + 1);
                    if (idx > 0)
                        val = work_order.Substring(0, idx);
                }
            }
            catch (Exception ex)
            {

            }
            return val;
        }

        private static List<dynamic> currentAndNextProductionPlans(int cell_id, int cell_shift_id, bool is_current_shift, out ProductionHistoryEntity most_recent, out int? current_production_plan_id)
        {
            most_recent = null;
            current_production_plan_id = null;
            var list = new List<dynamic>();
            try
            {
                using (var adapter = new DataAccessAdapter())
                {
                    var qf = new QueryFactory();

                    var phq = qf.ProductionHistory
                        .From(QueryTarget
                            .LeftJoin(qf.ReasonCode).On(ReasonCodeFields.ReasonCodeId == ProductionHistoryFields.ReasonCodeId)
                        )
                        .WithPath(ProductionHistoryEntity.PrefetchPathReasonCode.WithSubPath(ReasonCodeEntity.PrefetchPathRollupDefinition))
                        .Where(ProductionHistoryFields.CellShiftId == cell_shift_id)
                        .AndWhere(ReasonCodeFields.Scrap == false)
                        .OrderBy(ProductionHistoryFields.StartTime.Descending())
                        .OrderBy(ProductionHistoryFields.EndTime.Descending())
                        .OrderBy(ProductionHistoryFields.ProductionHistoryId.Descending())
                        .Limit(1);
                    most_recent = adapter.FetchFirst(phq);

                    if (is_current_shift)
                    {
                        var query = qf.Create()
                        .Select(() => new
                        {
                            // Production Plan
                            ProductionPlanId = ProductionPlanFields.ProductionPlanId.ToValue<int>(),
                            ProductId = ProductionPlanFields.ProductId.ToValue<int>(),
                            WorkOrder = ProductionPlanFields.WorkOrder.ToValue<string>(),
                            OperationId = ProductionPlanFields.OperationId.ToValue<int>(),
                            OperationPass = ProductionPlanFields.OperationPass.ToValue<int>(),
                            Planned = ProductionPlanFields.Planned.ToValue<int>(),
                            Actual = ProductionPlanFields.Actual.ToValue<int>(),
                            ActualDate = ProductionPlanFields.ActualDate.ToValue<DateTime?>(),

                            // Product
                            Sku = ProductFields.Sku.ToValue<string>(),

                            // ProductOperationTime
                            SetupTime = ProductOperationTimeFields.SetupTime.ToValue<int>(),
                            PartTime = ProductOperationTimeFields.PartTime.ToValue<int>(),
                            CycleTime = ProductOperationTimeFields.CycleTime.ToValue<int>(),
                            LoadUnloadTime = ProductOperationTimeFields.LoadUnloadTime.ToValue<int>()
                        })
                        .From(qf.ProductionPlan
                                .InnerJoin(qf.Product).On(ProductionPlanFields.ProductId == ProductFields.ProductId)
                                .InnerJoin(qf.ProductOperationTime).On(ProductOperationTimeFields.ProductId == ProductionPlanFields.ProductId)
                                )
                        .Where(ProductionPlanFields.CellId == cell_id)
                        .AndWhere(ProductionPlanFields.Closed == 0)
                        .AndWhere(ProductionPlanFields.Enabled == 1)
                        .OrderBy(ProductionPlanFields.SortOrder.Ascending())
                        .OrderBy(ProductionPlanFields.DueDate.Descending());

                        var items = adapter.FetchQuery(query);

                        if (items.Any() && most_recent != null && most_recent.ProductionPlanId.HasValue)
                        {
                            foreach (var item in items)
                            {
                                if (item.ProductionPlanId == most_recent.ProductionPlanId)
                                {
                                    list.Add(item);
                                    current_production_plan_id = item.ProductionPlanId;
                                }
                                else if (list.Any())
                                {
                                    list.Add(item);
                                    break;
                                }
                            }
                            // If the last one is selected, then make the first one if it exists the next one
                            if (list.Count() == 1 && items[0].ProductionPlanId != items[0].ProductionPlanId)
                            {
                                list.Add(items[0]);
                            }
                        }

                        if (!list.Any())
                        {
                            list.Add(null);
                            list.Add(items.Any() ? items[0] : null);
                        }

                    }
                    else
                    {
                        // Return the last Production Plan info for the shift
                        if (most_recent != null && most_recent.ProductionPlanId.HasValue)
                        {
                            var query = qf.Create()
                           .Select(() => new
                           {
                               // Production Plan
                               ProductionPlanId = ProductionPlanFields.ProductionPlanId.ToValue<int>(),
                               ProductId = ProductionPlanFields.ProductId.ToValue<int>(),
                               WorkOrder = ProductionPlanFields.WorkOrder.ToValue<string>(),
                               OperationId = ProductionPlanFields.OperationId.ToValue<int>(),
                               OperationPass = ProductionPlanFields.OperationPass.ToValue<int>(),
                               Planned = ProductionPlanFields.Planned.ToValue<int>(),
                               Actual = ProductionPlanFields.Actual.ToValue<int>(),
                               ActualDate = ProductionPlanFields.ActualDate.ToValue<DateTime?>(),

                               // Product
                               Sku = ProductFields.Sku.ToValue<string>(),

                               // ProductOperationTime
                               SetupTime = ProductOperationTimeFields.SetupTime.ToValue<int>(),
                               PartTime = ProductOperationTimeFields.PartTime.ToValue<int>(),
                               CycleTime = ProductOperationTimeFields.CycleTime.ToValue<int>(),
                               LoadUnloadTime = ProductOperationTimeFields.LoadUnloadTime.ToValue<int>()
                           })
                           .From(qf.ProductionPlan
                                   .InnerJoin(qf.Product).On(ProductionPlanFields.ProductId == ProductFields.ProductId)
                                   .InnerJoin(qf.ProductOperationTime).On(ProductOperationTimeFields.ProductId == ProductionPlanFields.ProductId)
                                   )
                           .Where(ProductionPlanFields.ProductionPlanId == most_recent.ProductionPlanId);

                            var items = adapter.FetchQuery(query);
                            if (items.Any())
                            {
                                list.Add(items.First());
                                list.Add(null);
                            }

                        }

                        if (!list.Any())
                        {
                            list.Add(null);
                            list.Add(null);
                        }
                    }

                }

            }
            catch (Exception e)
            {
                FileLog.Error("currentAndNextProductionPlans() - ", e);
            }

            return list;
        }

        public static ProductionDataResponse GetProductionData(ProductionDataRequest request)
        {
            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    var now = DateTime.Now;
                    var qf = new QueryFactory();

                    #region Alarm colors
                    var alarm_qry = qf.AlarmSetting.Where(AlarmSettingFields.AreaId == request.AreaId).OrderBy(AlarmSettingFields.MinPercentage100Good.Ascending());
                    var alarm_colors = (EntityCollection<AlarmSettingEntity>)adapter.FetchQuery(alarm_qry);
                    #endregion

                    var area_shift = new AreaShiftEntity(request.AreaShiftId);
                    adapter.FetchEntity(area_shift);
                    var shift_start = new DateTime(request.ShiftDate.Year, request.ShiftDate.Month, request.ShiftDate.Day, area_shift.StartTime.Hours, area_shift.StartTime.Minutes, area_shift.StartTime.Seconds);
                    var shift_end = new DateTime(request.ShiftDate.Year, request.ShiftDate.Month, request.ShiftDate.Day, area_shift.EndTime.Hours, area_shift.EndTime.Minutes, area_shift.EndTime.Seconds);
                    if (shift_end <= shift_start)
                        shift_end = shift_end.AddDays(1);

                    bool is_current_shift = (now >= shift_start && now < shift_end);

                    #region Cell Button Status
                    var list = new List<CellButtonStatusItem>();
                    var cell_lookup = new Dictionary<int, CellButtonStatusItem>();

                    string zero_font_color = "White";
                    var zero_color = alarmColor(0, alarm_colors, out zero_font_color);

                    double diff;

                    if (is_current_shift)
                    {
                        var qry = qf.Create()
                            .Select(() => new
                            {
                                CellId = CellFields.CellId.ToValue<int>(),
                                RollupColor = RollupDefinitionFields.Color.ToValue<string>(),
                                RollupFontColor = RollupDefinitionFields.FontColor.ToValue<string>(),
                                FlashDelay = ReasonCodeFields.FlashDelay.ToValue<int?>(),
                                Offline = CellStatusFields.Offline.ToValue<bool?>(),
                                EventTime = CellStatusFields.EventTime.ToValue<DateTime?>(),
                                LastSync = CellStatusFields.LastSync.ToValue<DateTime?>()
                            })
                           .From(qf.Cell
                                .LeftJoin(qf.CellStatus).On(CellStatusFields.CellId == CellFields.CellId)
                                .LeftJoin(qf.ReasonCode).On(CellStatusFields.ReasonCodeId == ReasonCodeFields.ReasonCodeId)
                                .LeftJoin(qf.RollupDefinition).On(ReasonCodeFields.RollupDefinitionId == RollupDefinitionFields.RollupDefinitionId))
                            .Where(CellFields.AreaId == request.AreaId);

                        var statuses = adapter.FetchQuery(qry);

                        foreach (var status in statuses)
                        {
                            var cbsi = new CellButtonStatusItem
                            {
                                CellId = status.CellId,
                                RollupColor = status.RollupColor,
                                RollupFontColor = status.RollupFontColor,
                                Offline = status.Offline ?? false,
                                Percent = 0,
                                Flash = false,
                                AlarmColor = zero_color,
                                AlarmFontColor = zero_font_color,
                                TabletOffline = false
                            };

                            if (!status.EventTime.HasValue || status.EventTime.Value < shift_start)
                            {
                                cbsi.Flash = false;
                                cbsi.Offline = true;
                                cbsi.TabletOffline = true;
                                cbsi.NoData = true;
                            }
                            else
                            {
                                cbsi.NoData = false;
                                diff = (now - status.LastSync.Value).TotalSeconds;
                                if (diff > 180.0)
                                {
                                    cbsi.TabletOffline = true;
                                }

                                if (status.FlashDelay.HasValue)
                                {
                                    diff = (now - status.EventTime.Value).TotalSeconds;
                                    if (diff > status.FlashDelay.Value)
                                        cbsi.Flash = true;
                                }

                            }

                            list.Add(cbsi);
                            cell_lookup.Add(cbsi.CellId, cbsi);
                        }
                    }
                    else
                    {
                        var cell_qry = qf.Cell
                            .Select(CellFields.CellId)
                            .From(QueryTarget.InnerJoin(qf.CellShift).On(CellShiftFields.CellId == CellFields.CellId)
                                .InnerJoin(qf.ProductionHistory).On(ProductionHistoryFields.CellShiftId == CellShiftFields.CellShiftId))
                            .Where(CellShiftFields.AreaShiftId == request.AreaShiftId)
                            .AndWhere(DateTimeFunctions.DayOfYear(CellShiftFields.StartTime).Equal(DateTimeFunctions.DayOfYear(shift_start)))
                            .AndWhere(DateTimeFunctions.Year(CellShiftFields.StartTime).Equal(DateTimeFunctions.Year(shift_start))).Distinct();

                        var qry = qf.Create()
                            .Select(() => new CellButtonStatusItem
                            {
                                CellId = CellFields.CellId.Source("c").ToValue<int>(),
                                RollupColor = "#FFFFFFFF",
                                RollupFontColor =  "Transparent",
                                AlarmColor = zero_color,
                                AlarmFontColor = zero_font_color,
                                Percent = 0,
                                Flash = false,
                                Offline = false,
                                TabletOffline = false,
                                NoData = false
                            })
                           .From(qf.Cell.As("c"))
                           .Where(CellFields.CellId.Source("c").In(cell_qry));
                        list = adapter.FetchQuery(qry);

                        var qry2 = qf.Create()
                            .Select(() => new CellButtonStatusItem
                            {
                                CellId = CellFields.CellId.Source("c").ToValue<int>(),
                                RollupColor = "#FFFFFFFF",
                                RollupFontColor = "Transparent",
                                AlarmColor = "#FFFFFFFF",
                                AlarmFontColor = "Transparent",
                                Percent = 0,
                                Flash = false,
                                Offline = false,
                                TabletOffline = false,
                                NoData = true
                            })
                           .From(qf.Cell.As("c"))
                           .Where(CellFields.CellId.Source("c").NotIn(cell_qry));
                        var list2 = adapter.FetchQuery(qry2);

                        list = list.Concat(list2).ToList();

                        foreach (var status in list)
                        {
                            cell_lookup.Add(status.CellId, status);
                        }
                    }

                    #endregion

                    #region Specific Cell Production Data

                    CellProductionData cell_production_data = null;
                    if (request.CellId != 0)
                    {
                        cell_production_data = cellProductionData(request.CellId, request.AreaId, request.AreaShiftId, shift_start, shift_end, is_current_shift);
                    }
  
                    #endregion

                    #region Area Production Data

                    AreaProductionData area_production_data = areaProductionData(request.AreaId, request.AreaShiftId, shift_start, shift_end, is_current_shift, cell_lookup, alarm_colors);

                    #endregion

                    var response = new ProductionDataResponse
                    {
                        Success = true,
                        CellStatus = list,
                        CellProductionData = cell_production_data,
                        AreaProductionData = area_production_data,
                        IsCurrentShift = is_current_shift
                    };

                    return response;
                }
                catch (Exception ex)
                {
                    FileLog.Error("GetProductionData", ex);
                    return new ProductionDataResponse { Success = false, Error = BusinessLogicError.GetReasonCodeFailed };
                }
            }
        }

        static public CellProductionData cellProductionData(int cell_id, int area_id, int area_shift_id, DateTime shift_start, DateTime shift_end, bool is_current_shift)
        {

            var response = new CellProductionData();
            response.Success = false;

            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    response.Legend = new List<LegendItem>();
                    response.Axis = new List<AxisDetailItem>();
                    response.GraphData = new ObservableCollection<ProductionMeterDataPoint>();

                    var qf = new QueryFactory();

                    var now = DateTime.Now;
                    int meter_width = 1;
                    var area = new AreaEntity(area_id);
                    if (adapter.FetchEntity(area))
                    {
                        meter_width = area.ProductionMeterWidth;
                        response.ProductionMeterWidth = meter_width * 60;
                        response.ProductionMeterFreq = area.ProductionMeterLabelFreq;
                    }
                    else
                    {
                        FileLog.Error("cellProductionData() - unknown area id");
                        return null;
                    }

                    //
                    // Legend
                    //
                    var qry = qf.RollupDefinition
                        .Where(RollupDefinitionFields.AreaId == area_id)
                        .OrderBy(RollupDefinitionFields.SortOrder.Ascending());
                    var rdc = new EntityCollection(new RollupDefinitionEntityFactory());
                    adapter.FetchQuery(qry, rdc);

                    var legend_lookup = new Dictionary<int, int>();
                    int idx = 0;
                    foreach (RollupDefinitionEntity rde in rdc)
                    {
                        response.Legend.Add(new LegendItem { Description = rde.Description, Color = rde.Color, RollupDefinitionId = rde.RollupDefinitionId });
                        legend_lookup.Add(rde.RollupDefinitionId, idx);
                        ++idx;
                    }

                    rdc = null;
                    qry = null;
                 
                    //
                    // Start and End of shift
                    //
                    int cell_shift_id = 0;
                    response.StartTime = shift_start;
                    response.EndTime = shift_end;

                    //  Are we looking at historical data and not current data
                    if (now >= shift_end)
                        now = shift_end;

                    var cell_shift_qry = qf.CellShift
                        .Where(CellShiftFields.AreaShiftId == area_shift_id)
                        .AndWhere(CellShiftFields.CellId == cell_id)
                        .AndWhere(DateTimeFunctions.DayOfYear(CellShiftFields.StartTime).Equal(DateTimeFunctions.DayOfYear(shift_start)))
                        .AndWhere(DateTimeFunctions.Year(CellShiftFields.StartTime).Equal(DateTimeFunctions.Year(shift_start)));
                    var cell_shift = adapter.FetchQuery(cell_shift_qry);
                    if (cell_shift != null && cell_shift.Count > 0)
                    {
                        cell_shift_id = ((CellShiftEntity)cell_shift[0]).CellShiftId;
                    }


                    ProductionHistoryEntity most_recent = null;
                    int? current_production_plan_id = null;
                    var production_plans = currentAndNextProductionPlans(cell_id, cell_shift_id, is_current_shift, out most_recent, out current_production_plan_id);

                    if (most_recent != null)
                    {
                        if (most_recent.ReasonCode == null || most_recent.ReasonCode.Category == (int)ReasonCodeItem.ReasonCodeCategory.CycleTime)
                        {
                            response.DowntimeDescription = "";
                        }
                        else
                        {
                            response.DowntimeDescription = (most_recent.ReasonCode.Description == "Load / Unload Overrun") ? "Waiting for operator" : most_recent.ReasonCode.Description;
                            response.DowntimeDescription += "\nsince " + most_recent.StartTime.ToString("d-MMM-yyyy h:mm tt");
                            response.DowntimeBackground = most_recent.ReasonCode.RollupDefinition.Color;
                            response.DowntimeForeground = most_recent.ReasonCode.RollupDefinition.FontColor;
                        }
                    }


                    // y-axis
                    var start = new DateTime(shift_start.Year, shift_start.Month, shift_start.Day, shift_start.Hour, 0, 0);
                    var end = start.AddHours(meter_width);

                    double ave_cycle_time = 0;
                    double ave_loadunload_time = 0;
                    double partial_ave_cycle_time = 0;
                    double partial_ave_loadunload_time = 0;

                    int parts_produced = 0;
                    double secs;
                    ReasonCodeItem.ReasonCodeCategory last_category = ReasonCodeItem.ReasonCodeCategory.None;

                    // data
                    var qry2 = qf.ProductionHistory
                        .Where(ProductionHistoryFields.CellShiftId == cell_shift_id)
                        .WithPath(ProductionHistoryEntity.PrefetchPathReasonCode)
                        .OrderBy(ProductionHistoryFields.StartTime.Ascending())
                        .OrderBy(ProductionHistoryFields.EndTime.Ascending())
                        .OrderBy(ProductionHistoryFields.ProductionHistoryId.Ascending());
                    var phc = new EntityCollection(new ProductionHistoryEntityFactory());
                    adapter.FetchQuery(qry2, phc);

                    ProductionMeterDataPoint dp = null;
                    DateTime ss = start, ee = end;
                    int legend_idx = 0;

                    // Create y-axis
                    while (ss < shift_end)
                    {
                        var axis = new AxisDetailItem(ss, ee);
                        response.Axis.Add(axis);

                        ss = ss.AddHours(meter_width);
                        ee = ss.AddHours(meter_width);
                    }

                    int axis_idx = 0;

                    foreach (ProductionHistoryEntity phe in phc)
                    {
                        legend_idx = 0;
                        if (phe.ReasonCode != null && phe.ReasonCode.RollupDefinitionId.HasValue)
                            legend_lookup.TryGetValue(phe.ReasonCode.RollupDefinitionId.Value, out legend_idx);
                        if (!phe.EndTime.HasValue)
                            phe.EndTime = now;

                        if (phe.ReasonCode != null && (phe.ReasonCode.Category == (int)ReasonCodeItem.ReasonCodeCategory.PartProduced
                                                       || phe.ReasonCode.Scrap))
                        {
                            while (phe.EndTime.Value > end)
                            {
                                end = end.AddHours(meter_width);
                                ++axis_idx;
                            }

                            if (phe.ReasonCode.Scrap)
                            {
                                int sv = (int)(phe.WorkContent.HasValue ? phe.WorkContent.Value : 1);
                                if (response.Axis[axis_idx].Defect.HasValue)
                                    response.Axis[axis_idx].Defect += sv;
                                else
                                    response.Axis[axis_idx].Defect = sv;
                            }
                            else
                            {
                                if (response.Axis[axis_idx].Good.HasValue)
                                    response.Axis[axis_idx].Good += 1;
                                else
                                    response.Axis[axis_idx].Good = 1;
                            }
                        }

                        response.GraphData.Add(new ProductionMeterDataPoint(phe.StartTime, phe.EndTime.Value, legend_idx));

                        // calculate average times
                        if (phe.ReasonCode != null && (!current_production_plan_id.HasValue || current_production_plan_id == phe.ProductionPlanId)
                            && (phe.ReasonCode.Category == (int)ReasonCodeItem.ReasonCodeCategory.CycleTime
                                || phe.ReasonCode.Category == (int)ReasonCodeItem.ReasonCodeCategory.LoadUnloadTime
                                || phe.ReasonCode.Category == (int)ReasonCodeItem.ReasonCodeCategory.PartProduced))
                        {
                            secs = phe.EndTime.HasValue ? (phe.EndTime.Value - phe.StartTime).TotalSeconds : (now - phe.StartTime).TotalSeconds;
                            last_category = (ReasonCodeItem.ReasonCodeCategory)phe.ReasonCode.Category;
                            switch (last_category)
                            {
                                case ReasonCodeItem.ReasonCodeCategory.CycleTime:
                                    partial_ave_cycle_time += secs;
                                    break;

                                case ReasonCodeItem.ReasonCodeCategory.LoadUnloadTime:
                                    partial_ave_loadunload_time += secs;
                                    break;

                                case ReasonCodeItem.ReasonCodeCategory.PartProduced:
                                    // Only use full complete cycles in calculation; that is, up to the last PartProduced
                                    ave_cycle_time += partial_ave_cycle_time;
                                    ave_loadunload_time += partial_ave_loadunload_time;
                                    ++parts_produced;
                                    partial_ave_cycle_time = 0;
                                    partial_ave_loadunload_time = 0;
                                    break;
                                default:
                                    break;
                            }
                        }

                    }

                    var last = phc.Any() ? phc.Last() as ProductionHistoryEntity : null;
                    if (last != null)
                    {
                        if (!last.EndTime.HasValue)
                            last.EndTime = now;
                        while (last.EndTime.Value > end)
                        {
                            end = end.AddHours(meter_width);
                            ++axis_idx;
                        }

                        for (int jj = axis_idx; jj >= 0; --jj)
                        {
                            if (!response.Axis[jj].Good.HasValue)
                                response.Axis[jj].Good = 0;
                        }
                    }


                    if (parts_produced > 0)
                    {
                        ave_cycle_time /= parts_produced;
                        ave_loadunload_time /= parts_produced;
                    }

                    phc = null;
                    qry2 = null;

                    legend_lookup.Clear();

                    TimeSpan ts;
                    if (production_plans.Any() && production_plans[0] != null)
                    {
                        response.CurrentSku = production_plans[0].Sku;
                        response.CurrentWorkOrder = production_plans[0].WorkOrder;
                        response.CurrentQuantity = production_plans[0].Planned.ToString("#,##0");
                        response.CurrentRemaining = (production_plans[0].Planned - production_plans[0].Actual).ToString("#,##0");
                        response.CurrentPass = production_plans[0].OperationPass.ToString();

                        ts = new TimeSpan(0, 0, production_plans[0].CycleTime > 0 ? production_plans[0].CycleTime : DEFAULT_CYCLE_TIME);
                        response.TargetCycleTime = string.Format("{0}:{1:00}", ts.Minutes, ts.Seconds);

                        ts = new TimeSpan(0, 0, production_plans[0].LoadUnloadTime > 0 ? production_plans[0].LoadUnloadTime : DEFAULT_LOADUNLOAD_TIME);
                        response.TargetLoadUnloadTime = string.Format("{0}:{1:00}", ts.Minutes, ts.Seconds);
                    }
                    else
                    {
                        response.CurrentSku = "";
                        response.CurrentWorkOrder = "";
                        response.CurrentQuantity = "";
                        response.CurrentRemaining = "";
                        response.CurrentPass = "";
                        response.TargetCycleTime = string.Format("{0}:{1:00}", 1, 0);
                        response.TargetLoadUnloadTime = string.Format("{0}:{1:00}", 0, 30);
                    }

                    if (production_plans.Count() > 1 && production_plans[1] != null)
                    {
                        response.NextSku = production_plans[1].Sku;
                        response.NextWorkOrder = production_plans[1].WorkOrder;
                        response.NextQuantity = production_plans[1].Planned.ToString("#,##0");
                        response.NextRemaining = (production_plans[1].Planned - production_plans[1].Actual).ToString("#,##0");
                        response.NextPass = production_plans[1].OperationPass.ToString();
                    }
                    else
                    {
                        response.NextSku = "";
                        response.NextWorkOrder = "";
                        response.NextQuantity = "";
                        response.NextRemaining = "";
                        response.NextPass = "";
                    }

                    ts = new TimeSpan(0, 0, (int)Math.Round(ave_cycle_time));
                    response.AverageCycleTime = string.Format("{0}:{1:00}", ts.Minutes, ts.Seconds);

                    ts = new TimeSpan(0, 0, (int)Math.Round(ave_loadunload_time));
                    response.AverageLoadUnloadTime = string.Format("{0}:{1:00}", ts.Minutes, ts.Seconds);

                    // Downtime graph

                    response.DowntimeGraphTitle = string.Format("TOP DOWNTIME HOURS {0} - {1}", shift_start.ToString("h:mm tt"), shift_end.ToString("h:mm tt"));    
                    var downtime_qry = qf.Create()
                        .Select(() => new DowntimeGraphDataItem
                        {
                            Color = RollupDefinitionFields.Color.Source("rd").ToValue<string>(),
                            Description = ReasonCodeFields.Description.Source("rc").ToValue<string>(),
                            StartTime = ProductionHistoryFields.StartTime.Source("ph").ToValue<DateTime>(),
                            EndTime = ProductionHistoryFields.EndTime.Source("ph").ToValue<DateTime?>(),
                            DowntimeAckTime = ProductionHistoryFields.DowntimeAckTime.Source("ph").ToValue<DateTime?>(),
                            IgnoreInWaitingCalc = ReasonCodeFields.IgnoreInWaitingCalc.Source("rc").ToValue<bool>()
                        })
                        .From(qf.ProductionHistory.As("ph")
                                .InnerJoin(ProductionHistoryEntity.Relations.ReasonCodeEntityUsingReasonCodeId, "ph", "rc")
                                .InnerJoin(ReasonCodeEntity.Relations.RollupDefinitionEntityUsingRollupDefinitionId, "rc", "rd"))
                        .Where(ProductionHistoryFields.CellShiftId.Source("ph") == cell_shift_id)
                        .AndWhere(ReasonCodeFields.DisplayInDowntimeChart.Source("rc") == 1);
                    var val = adapter.FetchQuery(downtime_qry);
                    response.DowntimeGraphData = val;

                    // Operator
                    string operator_name = "";
                    if (last != null && last.OperatorId.HasValue)
                    {
                        var oe = new OperatorEntity(last.OperatorId.Value);
                        if (adapter.FetchEntity(oe))
                        {
                            operator_name = oe.FirstName;
                            if (string.IsNullOrWhiteSpace(operator_name))
                                operator_name = oe.LastName;
                            else if (!string.IsNullOrWhiteSpace(oe.LastName))
                                operator_name += " " + oe.LastName;
                        }
                    }

                    response.Operator = operator_name;

                    qf = null;

                }
                catch (Exception ex)
                {
                    FileLog.Error("cellProductionData() - ", ex);
                }
            }

            return response;
        }

        static private string alarmColor(int percent, EntityCollection<AlarmSettingEntity> alarm_colors, out string font_color)
        {
            string alarm_color = "#FF0000";
            font_color = "#FFFFFF";

            foreach (var ase in alarm_colors)
            {
                if (ase.MinPercentage100Good <= percent)
                {
                    alarm_color = ase.Color;
                    font_color = ase.FontColor;
                }
                else if (ase.MinPercentage100Good > percent)
                {
                    break;
                }
            }

            return alarm_color;
        }

        static public AreaProductionData areaProductionData(int area_id, int area_shift_id, DateTime shift_start, DateTime shift_end, bool is_current_shift, Dictionary<int, CellButtonStatusItem> cell_status_lookup, EntityCollection<AlarmSettingEntity> alarm_colors)
        {

            var response = new AreaProductionData();
            response.Success = false;
            DateTime now = DateTime.Now;
            double secs_per_hour = 3600;
            string font_color = "";

            using (var adapter = new DataAccessAdapter())
            {
                try
                {

                    var qf = new QueryFactory();

                    //  Are we looking at historical data and not current data
                    if (now >= shift_end)
                        now = shift_end;

                    // Legend

                    var qry = qf.Create()
                        .Select(() => new LegendItem
                        {
                            Description = RollupDefinitionFields.Description.Source("rd").ToValue<string>(),
                            Color = RollupDefinitionFields.Color.Source("rd").ToValue<string>(),
                            RollupDefinitionId = RollupDefinitionFields.RollupDefinitionId.Source("rd").ToValue<int>()
                        })
                        .From(qf.RollupDefinition.As("rd"))
                        .Where(RollupDefinitionFields.AreaId.Source("rd") == area_id)
                        .OrderBy(RollupDefinitionFields.SortOrder.Source("rd").Ascending());
                    response.Legend = adapter.FetchQuery(qry);
                    qry = null;


                    // Downtime graph

                    response.DowntimeGraphTitle = string.Format("TOP DOWNTIME HOURS {0} - {1}", shift_start.ToString("h:mm tt"), shift_end.ToString("h:mm tt"));
                    var cell_shifts_qry = qf.CellShift
                        .Select(CellShiftFields.CellShiftId)
                        .Where(CellShiftFields.AreaShiftId == area_shift_id)
                        .AndWhere(DateTimeFunctions.DayOfYear(CellShiftFields.StartTime).Equal(DateTimeFunctions.DayOfYear(shift_start)))
                        .AndWhere(DateTimeFunctions.Year(CellShiftFields.StartTime).Equal(DateTimeFunctions.Year(shift_start)));

                    var active_cells = adapter.FetchScalar<int>(qf.Create().Select(cell_shifts_qry.CountRow()));

                    var downtime_qry = qf.Create()
                        .Select(() => new DowntimeGraphDataItem
                        {
                            Color = RollupDefinitionFields.Color.Source("rd").ToValue<string>(),
                            Description = ReasonCodeFields.Description.Source("rc").ToValue<string>(),
                            StartTime = ProductionHistoryFields.StartTime.Source("ph").ToValue<DateTime>(),
                            EndTime = ProductionHistoryFields.EndTime.Source("ph").ToValue<DateTime?>(),
                            DowntimeAckTime = ProductionHistoryFields.DowntimeAckTime.Source("ph").ToValue<DateTime?>(),
                            IgnoreInWaitingCalc = ReasonCodeFields.IgnoreInWaitingCalc.Source("rc").ToValue<bool>()
                        })
                        .From(qf.ProductionHistory.As("ph")
                                .InnerJoin(ProductionHistoryEntity.Relations.ReasonCodeEntityUsingReasonCodeId, "ph", "rc")
                                .InnerJoin(ReasonCodeEntity.Relations.RollupDefinitionEntityUsingRollupDefinitionId, "rc", "rd"))
                        .Where(ProductionHistoryFields.CellShiftId.Source("ph").In(cell_shifts_qry))
                        .AndWhere(ReasonCodeFields.DisplayInDowntimeChart.Source("rc") == 1);
                    var val = adapter.FetchQuery(downtime_qry);
                    response.DowntimeGraphData = val;

                    // Utilization graph
                    int[] categories = {
                        (int)ReasonCodeItem.ReasonCodeCategory.CycleTime,
                        (int)ReasonCodeItem.ReasonCodeCategory.LoadUnloadTime,
                        (int)ReasonCodeItem.ReasonCodeCategory.ScheduledDown
                    };
                    var utilization_qry = qf.Create()
                        .Select(() => new
                        {
                            StartTime = ProductionHistoryFields.StartTime.ToValue<DateTime>(),
                            EndTime = ProductionHistoryFields.EndTime.ToValue<DateTime?>(),
                            Category = ReasonCodeFields.Category.ToValue<int>(),
                            CellId = CellShiftFields.CellId.ToValue<int>()
                        })
                        .From(qf.ProductionHistory
                            .InnerJoin(qf.ReasonCode).On(ReasonCodeFields.ReasonCodeId == ProductionHistoryFields.ReasonCodeId)
                            .InnerJoin(qf.CellShift).On(CellShiftFields.CellShiftId == ProductionHistoryFields.CellShiftId)
                            )
                        .Where(ProductionHistoryFields.CellShiftId.In(cell_shifts_qry))
                        .AndWhere(ReasonCodeFields.Category.In(categories))
                        .OrderBy(ProductionHistoryFields.CellShiftId.Ascending());
                    var utilization_data = adapter.FetchQuery(utilization_qry);

                    var utilization = new List<GraphDataItem>();
                    var num = new List<double>();
                    var denom = new List<double>();

                    DateTime when = shift_start;
                    double diff;
                    DateTime end, ss, ee;
                    int idx;

                    double shift_total_secs = (now - shift_start).TotalSeconds;
                    int prev_cell_id = -1;
                    double cell_num = 0;
                    double cell_denom = shift_total_secs;
                    double percent;
                    CellButtonStatusItem cbsi;

                    while (when < shift_end)
                    {
                        utilization.Add(new GraphDataItem{ XVal = when.Hour, YVal = 0.0 });
                        num.Add(0.0);
                        diff = (now - when).TotalSeconds;
                        if (diff >= secs_per_hour)
                            denom.Add(active_cells * secs_per_hour);
                        else if (diff <= 0)
                            denom.Add(0.0);
                        else
                            denom.Add(active_cells * diff);
                        when = when.AddHours(1);
                    }

                    foreach (var item in utilization_data)
                    {
                        end = item.EndTime ?? now;
                        idx = (int)Math.Truncate((item.StartTime - shift_start).TotalHours);
                        if (item.StartTime.Hour == end.Hour)
                        {
                            diff = (end - item.StartTime).TotalSeconds;

                            if (item.Category == (int)ReasonCodeItem.ReasonCodeCategory.ScheduledDown)
                                denom[idx] -= diff;
                            else
                                num[idx] += diff;
                        }
                        else
                        {
                            // hard way
                            ss = item.StartTime;
                            ee = new DateTime(ss.Year, ss.Month, ss.Day, ss.Hour, 0, 0);
                            ee = ee.AddHours(1);  // we cannot just have ss.Hour + 1 because this could wrap to next day.

                            while (ss < end)
                            {
                                diff = (ee - ss).TotalSeconds;

                                if (item.Category == (int)ReasonCodeItem.ReasonCodeCategory.ScheduledDown)
                                    denom[idx] -= diff;
                                else
                                    num[idx] += diff;

                                ss = ee;
                                ee = ss.AddHours(1);
                                if (ee > end)
                                    ee = end;
                                ++idx;
                            }
                        }

                        // cell specific shift_utilization
                        if (item.CellId != prev_cell_id)
                        {
                            if (prev_cell_id > 0 && cell_status_lookup.TryGetValue(prev_cell_id, out cbsi))
                            {
                                if (cell_denom <= 0.0)
                                    percent = -1;  // all schedule down
                                else if (cell_num <= 0.0)
                                    percent = 0.0;  // all downtime, no cycle or load/unload
                                else
                                    percent = 100.0 * cell_num / cell_denom;

                                cbsi.Percent = (int)Math.Round(percent);
                                if (percent < 0)
                                {
                                    cbsi.AlarmColor = SCHEDULE_DOWN_GRAY;
                                    cbsi.AlarmFontColor = "Transparent";
                                }
                                else
                                {
                                    cbsi.AlarmColor = alarmColor(cbsi.Percent, alarm_colors, out font_color);
                                    cbsi.AlarmFontColor = font_color;
                                }
                                cbsi.NoData = false;
                            }
                            cell_num = 0;
                            cell_denom = shift_total_secs;
                            prev_cell_id = item.CellId;
                        }
                        diff = (end - item.StartTime).TotalSeconds;
                        if (item.Category == (int)ReasonCodeItem.ReasonCodeCategory.ScheduledDown)
                            cell_denom -= diff;
                        else
                            cell_num += diff;
                    }

                    // last cell utilization value
                    if (prev_cell_id > 0 && cell_status_lookup.TryGetValue(prev_cell_id, out cbsi))
                    {
                        if (cell_denom <= 0.0)
                            percent = -1;  // all schedule down
                        else if (cell_num <= 0.0)
                            percent = 0.0;  // all downtime, no cycle or load/unload
                        else
                            percent = 100.0 * cell_num / cell_denom;

                        cbsi.Percent = (int)Math.Round(percent);
                        if (percent < 0)
                        {
                            cbsi.AlarmColor = SCHEDULE_DOWN_GRAY;
                            cbsi.AlarmFontColor = "Transparent";
                        }
                        else
                        {
                            cbsi.AlarmColor = alarmColor(cbsi.Percent, alarm_colors, out font_color);
                            cbsi.AlarmFontColor = font_color;
                        }
                        cbsi.NoData = false;
                    }

                    double plant_utilization = -1;
                    double plant_num = 0;
                    double plant_denom = 0;
                    for (int ii=0; ii<utilization.Count; ++ii)
                    {
                        if (denom[ii] > 0.0)
                            utilization[ii].YVal = 100.0 * num[ii] / denom[ii];
                        else
                            utilization[ii].YVal = -1.0;  // no data yet; to differentiate from 0%
                        plant_num += num[ii];
                        plant_denom += denom[ii];
                    }
                    if (plant_denom > 0.0)
                        plant_utilization = 100.0 * plant_num / plant_denom;

                    response.UtilizationGraphData = utilization;

                    //
                    // Plant Metrics
                    //

                    var plant_metrics = new PlantMetricsDataItem
                    {
                        MachinesScheduled = 0,
                        TotalMachines = 0,
                        MachinesScheduledForegroundColor = "#FF000000",
                        MachinesScheduledBackgroundColor = "#FFFFFFFF",

                        Utilization = 0,
                        UtilizationForegroundColor = "#00FFFFFF", // transparent
                        UtilizationBackgroundColor = "#FFFFFFFF", 

                        Productivity = 0,
                        ProductivityForegroundColor = "#00FFFFFF", // transparent
                        ProductivityBackgroundColor = "#FFFFFFFF", 

                        Setups = 0,
                        SetupsForegroundColor = "#00FFFFFF", // transparent
                        SetupsBackgroundColor = "#FFFFFFFF",
                        SetupEfficiency = 0,
                        SetupEfficiencyForegroundColor = "#00FFFFFF", // transparent
                        SetupEfficiencyBackgroundColor = "#FFFFFFFF"  
                    };

                    if (plant_utilization >= 0)
                    {
                        plant_metrics.Utilization = (int)Math.Round(plant_utilization);
                        plant_metrics.UtilizationBackgroundColor = alarmColor(plant_metrics.Utilization, alarm_colors, out font_color);
                        plant_metrics.UtilizationForegroundColor = font_color;
                    }

                    // Scheduled machines
                    if (is_current_shift)
                    {
                        var assigned_machines_qry = qf.Cell
                            .Select(CellFields.CellId)
                            .Where(CellFields.Assigned == 1);
                        var scheduled_machines_qry = qf.ProductionPlan
                            .Select(ProductionPlanFields.CellId)
                            .From(QueryTarget.LeftJoin(qf.Cell).On(ProductionPlanFields.CellId == CellFields.CellId))
                            .Where(CellFields.Assigned == 1)
                            .AndWhere(ProductionPlanFields.Enabled == 1)
                            .AndWhere(ProductionPlanFields.Closed == 0);

                        plant_metrics.TotalMachines = adapter.FetchScalar<int>(qf.Create().Select(assigned_machines_qry.CountRow()));
                        plant_metrics.MachinesScheduled = adapter.FetchScalar<int>(qf.Create().Select(scheduled_machines_qry.CountDistinct(ProductionPlanFields.CellId)));
                    }
                    else
                    {
                        plant_metrics.TotalMachines = -1;
                        plant_metrics.MachinesScheduled = -1;
                        plant_metrics.MachinesScheduledForegroundColor = "#00FFFFFF";
                    }

                    // Setup
                    var setup_qry = qf.Create()
                        .Select(() => new
                        {
                            StartTime = ProductionHistoryFields.StartTime.ToValue<DateTime>(),
                            EndTime = ProductionHistoryFields.EndTime.ToValue<DateTime?>(),
                            ProfitFabSetupTime = ProductOperationTimeFields.SetupTime.ToValue<int?>(),
                        })
                        .From(qf.ProductionHistory
                            .InnerJoin(qf.ReasonCode).On(ReasonCodeFields.ReasonCodeId == ProductionHistoryFields.ReasonCodeId)
                            .InnerJoin(qf.CellShift).On(CellShiftFields.CellShiftId == ProductionHistoryFields.CellShiftId)
                            .LeftJoin(qf.ProductionPlan).On(ProductionPlanFields.ProductionPlanId == ProductionHistoryFields.ProductionPlanId)
                            .LeftJoin(qf.ProductOperationTime).On(ProductOperationTimeFields.ProductId == ProductionPlanFields.ProductId)
                            //.And(ProductOperationTimeFields.WorkOrderField1 == ProductionPlanFields.WorkOrderField1))
                            )
                        .Where(ProductionHistoryFields.CellShiftId.In(cell_shifts_qry))
                        .AndWhere(ProductionHistoryFields.EndTime.IsNotNull())  // only count complete setups
                        .AndWhere(ReasonCodeFields.Category == (int)ReasonCodeItem.ReasonCodeCategory.SetupTime)
                        .OrderBy(ProductionHistoryFields.CellShiftId.Ascending());
                    var setup_data = adapter.FetchQuery(setup_qry);

                    if (setup_data != null)
                    {
                        plant_metrics.Setups = setup_data.Count;
                        plant_metrics.SetupsForegroundColor = "#FF000000";

                        double total_actual = 0.0;
                        double total_profitfab = 0.0;
                        foreach (var setup in setup_data)
                        {
                            if (setup.ProfitFabSetupTime.HasValue && setup.EndTime.HasValue)
                            {
                                total_actual = (setup.EndTime.Value - setup.StartTime).TotalSeconds;
                                total_profitfab += setup.ProfitFabSetupTime.Value;
                            }
                        }

                        if (total_actual > 0)
                        {
                            plant_metrics.SetupEfficiency = (int)Math.Round(100.0 * total_profitfab / total_actual);
                            plant_metrics.SetupEfficiencyBackgroundColor = alarmColor(plant_metrics.SetupEfficiency, alarm_colors, out font_color);
                            plant_metrics.SetupEfficiencyForegroundColor = font_color;
                        }

                    }

                    response.PlantMetrics = plant_metrics;

                    qf = null;

                }
                catch (Exception ex)
                {
                    FileLog.Error("productionMeterData() - ", ex);
                }
            }

            return response;
        }

    }
}
